package com.elitecrm.service.coresync;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.dom4j.Document;
import org.dom4j.DocumentFactory;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

import com.chinainsurance.application.prpall.travel.AcciCarApplyInfSoapBindingStub;
import com.elite.algorithm.GUID;
import cn.com.elitecrm.util.StringUtil;

public class CoreSyncService {
	private static final Log log = LogFactory.getLog(CoreSyncService.class);
	private String endpoint;
	private List<DataSource> dataSources;
	private String queryRelationSQL;
	private String procedureName;
	
	public String getEndpoint() {
		return endpoint;
	}

	public void setEndpoint(String endpoint) {
		this.endpoint = endpoint;
	}

	public List<DataSource> getDataSources() {
		return dataSources;
	}

	public void setDataSources(List<DataSource> dataSources) {
		this.dataSources = dataSources;
	}

	public String getQueryRelationSQL() {
		return queryRelationSQL;
	}

	public void setQueryRelationSQL(String queryRelationSQL) {
		this.queryRelationSQL = queryRelationSQL;
	}

	public String getProcedureName() {
		return procedureName;
	}

	public void setProcedureName(String procedureName) {
		this.procedureName = procedureName;
	}

	public void syncCore(){
		log.debug("----------------- Start call sync core service ----------------");
		try {
			if(dataSources!=null && dataSources.size()>0){
				for(DataSource dataSource : dataSources){
					JdbcTemplate jt = new JdbcTemplate(dataSource);
					List<Map<String,Object>> relationList = jt.queryForList(queryRelationSQL);
					if(relationList!=null && relationList.size()>0){
						log.debug("Find "+relationList.size()+" relations.");
						for(Map<String,Object> relationMap : relationList){
							String relationId = (String)relationMap.get("relation_id");
							
							syncCoreSystemByRelationId(relationId, dataSource);
						}
					}
				}
			}
		} catch (Exception e) {
			log.error("",e);
		}
		log.debug("----------------- End call sync core service ----------------");
	}
	
	public void syncCoreSystemByRelationId(String relationId, DataSource dataSource) throws Exception{
		log.debug("Sync core system with relation_id "+relationId);
		JdbcTemplate jt = new JdbcTemplate(dataSource);
		Document requestDoc = DocumentFactory.getInstance().createDocument("utf-8");
		Element packetEle = requestDoc.addElement("Packet");
		Element headEle = packetEle.addElement("Head");
		Element bodyEle = packetEle.addElement("Body");
		
		boolean callEnabled = true;
		List<Map<String,Object>> headList = jt.queryForList("select businesstype,agencycode,productcode,seqno,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as transtime from Ecar_insurance_info where relation_id=?",relationId);
		if(headList!=null && headList.size()>0){
			Map<String,Object> headMap = headList.get(0);
			headEle.addElement("BusinessType").addText(StringUtil.parseNull(headMap.get("businesstype")));
			headEle.addElement("AgencyCode").addText(StringUtil.parseNull(headMap.get("agencycode")));
			headEle.addElement("ProductCode").addText(StringUtil.parseNull(headMap.get("productcode")));
			headEle.addElement("SeqNo").addText(StringUtil.parseNull(headMap.get("seqno")));
			headEle.addElement("TransTime").addText(StringUtil.parseNull(headMap.get("transtime")));
		}else{
			log.warn("Not found head with relation_id " + relationId +" , stop calling webservice.");
			callEnabled = false;
		}
		log.debug("lori1:"+callEnabled);
		
		Element baseInfoEle = bodyEle.addElement("BaseInfo");
		List<Map<String,Object>> baseInfoList = jt.queryForList("select to_char(applydate,'yyyy-mm-dd') as applydate,to_char(trunc(sysdate),'yyyy-mm-dd') as inputdate,to_char(issuedate,'yyyy-mm-dd') as issuedate,to_char(startdate,'yyyy-mm-dd hh24:mi:ss') as startdate,to_char(enddate,'yyyy-mm-dd hh24:mi:ss') as enddate from Ecar_insurance_info where relation_id=?",relationId);
		if(baseInfoList!=null && baseInfoList.size()>0){
			Map<String,Object> baseInfo = baseInfoList.get(0);					
			baseInfoEle.addElement("ApplyDate").addText(StringUtil.parseNull(baseInfo.get("applydate")));
			baseInfoEle.addElement("InputDate").addText(StringUtil.parseNull(baseInfo.get("inputdate")));
			baseInfoEle.addElement("IssueDate").addText(StringUtil.parseNull(baseInfo.get("issuedate")));
			baseInfoEle.addElement("StartDate").addText(StringUtil.parseNull(baseInfo.get("startdate")));
			baseInfoEle.addElement("EndDate").addText(StringUtil.parseNull(baseInfo.get("enddate")));
		}else{
			log.warn("Not found baseInfo with relation_id " + relationId +" , stop calling webservice.");
			callEnabled = false;
		}
		log.debug("lori2:"+callEnabled);
		
		Element policyHolderEle = bodyEle.addElement("PolicyHolder");
		List<Map<String,Object>> policyHolderList = jt.queryForList("select cust_type1,name,idtype,id,to_char(birthday,'yyyy-mm-dd') as appbirthdate,gender,address,postcode,mobile,tel,conname,conmobile,contel,consex from Ecar_insurance_cust where relation_id=? and cust_type2=1",relationId);
		if(policyHolderList!=null && policyHolderList.size()>0){
			Map<String,Object> policyHolderMap = policyHolderList.get(0);
			policyHolderEle.addElement("AppType").addText(StringUtil.parseNull(policyHolderMap.get("cust_type1")));
			policyHolderEle.addElement("AppName").addText(StringUtil.parseNull(policyHolderMap.get("name")));
			policyHolderEle.addElement("AppIdentifyType").addText(StringUtil.parseNull(policyHolderMap.get("idtype")));
			policyHolderEle.addElement("AppIdentifyNo").addText(StringUtil.parseNull(policyHolderMap.get("id")));
			policyHolderEle.addElement("AppBirthDate").addText(StringUtil.parseNull(policyHolderMap.get("appbirthdate")));
			policyHolderEle.addElement("AppSex").addText(StringUtil.parseNull(policyHolderMap.get("gender")));
			policyHolderEle.addElement("AppAddress").addText(StringUtil.parseNull(policyHolderMap.get("address")));
			policyHolderEle.addElement("AppPostCode").addText(StringUtil.parseNull(policyHolderMap.get("postcode")));
			policyHolderEle.addElement("AppMobilePhone").addText(StringUtil.parseNull(policyHolderMap.get("mobile")));
			policyHolderEle.addElement("AppOfficePhone").addText(StringUtil.parseNull(policyHolderMap.get("tel")));
			policyHolderEle.addElement("ConName").addText(StringUtil.parseNull(policyHolderMap.get("conname")));
			policyHolderEle.addElement("ConMobile").addText(StringUtil.parseNull(policyHolderMap.get("conmobile")));
			policyHolderEle.addElement("ConOfficePhone").addText(StringUtil.parseNull(policyHolderMap.get("contel")));
			policyHolderEle.addElement("ConSex").addText(StringUtil.parseNull(policyHolderMap.get("consex")));
			
		}else{
			log.warn("Not found policyHolder with relation_id " + relationId +" , stop calling webservice.");
			callEnabled = false;
		}
		
		log.debug("lori3:"+callEnabled);
		
		List<Map<String,Object>> locationList = jt.queryForList("select provinceid,province_desc,cityid,city_desc from Ecar_insurance_cust where relation_id=? and cust_type2=1",relationId);
		if(locationList!=null && locationList.size()>0){
			Map<String,Object> location = locationList.get(0);
			Element LocationEle = policyHolderEle.addElement("Location");
			LocationEle.addAttribute("ProvinceId", StringUtil.parseNull(location.get("provinceid")));
			LocationEle.addAttribute("ProvinceName", StringUtil.parseNull(location.get("province_desc")));
			LocationEle.addAttribute("CityId", StringUtil.parseNull(location.get("cityid")));
			LocationEle.addAttribute("CityName", StringUtil.parseNull(location.get("city_desc")));
		}
		
		Element itemListEle = bodyEle.addElement("ItemList");
		Element itemInfoEle = itemListEle.addElement("ItemInfo");
		List<Map<String,Object>> itemInfoList = jt.queryForList("select projectcode,plantype,trim(to_char(PlanInsured,'999999999.99')) as pi,trim(to_char(PlanPrem,'999999999.99')) as pp,PlanCount,Quantity,occuptype,occupation,workkind,licenseno from Ecar_insurance_info  where relation_id=?",relationId);
		if(itemInfoList!=null && itemInfoList.size()>0){
			Map<String,Object> itemInfoMap = itemInfoList.get(0);
			itemInfoEle.addAttribute("ProjectCode", StringUtil.parseNull(itemInfoMap.get("projectcode")));
			itemInfoEle.addElement("PlanType").addText(StringUtil.parseNull(itemInfoMap.get("plantype")));
			itemInfoEle.addElement("PlanInsured").addText(StringUtil.parseNull(itemInfoMap.get("pi")));
			itemInfoEle.addElement("PlanPrem").addText(StringUtil.parseNull(itemInfoMap.get("pp")));
			itemInfoEle.addElement("PlanCount").addText(StringUtil.parseNull(itemInfoMap.get("PlanCount")));
			itemInfoEle.addElement("Quantity").addText(StringUtil.parseNull(itemInfoMap.get("Quantity")));
			itemInfoEle.addElement("OccupationType").addText(StringUtil.parseNull(itemInfoMap.get("occuptype")));
			itemInfoEle.addElement("OccupationCode").addText(StringUtil.parseNull(itemInfoMap.get("occupation")));
			itemInfoEle.addElement("Workkind").addText(StringUtil.parseNull(itemInfoMap.get("workkind")));
			itemInfoEle.addElement("licenseNo").addText(StringUtil.parseNull(itemInfoMap.get("licenseno")));
		}else{
			log.warn("Not found itemInfo with relation_id " + relationId +" , stop calling webservice.");
			callEnabled = false;
		}
		
		log.debug("lori4:"+callEnabled);
		
		Element insuredListEle = itemInfoEle.addElement("InsuredList");
		List<Map<String,Object>> insuredList = jt.queryForList("select cardno,name,idtype,id,to_char(birthday,'yyyy-mm-dd') as insuredbirthdate,gender,AppliRelation,address,postcode,conmobile from Ecar_insurance_cust where relation_id=? and cust_type2=2",relationId);
		if(insuredList!=null && insuredList.size()>0){
			Map<String,Object> insured = insuredList.get(0);
			Element insuredEle = insuredListEle.addElement("Insured");
			insuredEle.addAttribute("CardNo", StringUtil.parseNull(insured.get("cardno")));
			insuredEle.addAttribute("InsuredName", StringUtil.parseNull(insured.get("name")));
			insuredEle.addAttribute("IdentifyType", StringUtil.parseNull(insured.get("idtype")));
			insuredEle.addAttribute("IdentifyNo", StringUtil.parseNull(insured.get("id")));
			insuredEle.addAttribute("InsuredBirthDate", StringUtil.parseNull(insured.get("insuredbirthdate")));
			insuredEle.addAttribute("InsuredSex", StringUtil.parseNull(insured.get("gender")));
			insuredEle.addAttribute("AppliRelation", StringUtil.parseNull(insured.get("AppliRelation")));
			insuredEle.addAttribute("Address", StringUtil.parseNull(insured.get("address")));
			insuredEle.addAttribute("PostCode", StringUtil.parseNull(insured.get("postcode")));
			insuredEle.addAttribute("ConPhone", StringUtil.parseNull(insured.get("conmobile")));
		}else{
			log.warn("Not found insured with relation_id " + relationId +" , stop calling webservice.");
			callEnabled = false;
		}
		
		log.debug("lori5:"+callEnabled);

		Element paymentInfoEle = bodyEle.addElement("payment_info");
		
		List<Map<String,Object>> paymentInfo1List = jt.queryForList("select businessname,businessid,cardbank,card_no,effecperiod,systemno,authorizeno,tradetype,trim(to_char(amount,'999999999.99')) as amount,oldserialno from Ecar_insurance_info where relation_id=?",relationId);
		if(paymentInfo1List!=null && paymentInfo1List.size()>0){
			Map<String,Object> paymentInfo1 = paymentInfo1List.get(0);
			paymentInfoEle.addElement("businessname").addText(StringUtil.parseNull(paymentInfo1.get("businessname")));
			paymentInfoEle.addElement("businessid").addText(StringUtil.parseNull(paymentInfo1.get("businessid")));
			paymentInfoEle.addElement("cardbank").addText(StringUtil.parseNull(paymentInfo1.get("cardbank")));
			paymentInfoEle.addElement("cardno").addText(StringUtil.parseNull(paymentInfo1.get("card_no")));
			paymentInfoEle.addElement("effectiveperiod").addText(StringUtil.parseNull(paymentInfo1.get("effecperiod")));
			paymentInfoEle.addElement("systemno").addText(StringUtil.parseNull(paymentInfo1.get("systemno")));
			paymentInfoEle.addElement("authorizationno").addText(StringUtil.parseNull(paymentInfo1.get("authorizeno")));
			paymentInfoEle.addElement("tradetype").addText(StringUtil.parseNull(paymentInfo1.get("tradetype")));
			paymentInfoEle.addElement("amount").addText(StringUtil.parseNull(paymentInfo1.get("amount")));
			paymentInfoEle.addElement("oldserialno").addText(StringUtil.parseNull(paymentInfo1.get("oldserialno")));			
		}else{
			log.warn("Not found paymentInfo1 with relation_id " + relationId +" , stop calling webservice.");
			callEnabled = false;
		}
		
		log.debug("lori6:"+callEnabled);
		
		List<Map<String,Object>> paymentInfo2List = jt.queryForList("select REAL_ORDER_NO, to_char(PAID_DATE,'yyyymmdd') as tradedate,to_char(PAID_DATE,'hh24miss') as tradetime from ecar_insurance_info where relation_id=?",relationId);
		if(paymentInfo2List!=null && paymentInfo2List.size()>0){
			Map<String,Object> paymentInfo2 = paymentInfo2List.get(0);
			paymentInfoEle.addElement("tradeno").addText(StringUtil.parseNull(paymentInfo2.get("REAL_ORDER_NO")));
			paymentInfoEle.addElement("tradedate").addText(StringUtil.parseNull(paymentInfo2.get("tradedate")));
			paymentInfoEle.addElement("tradetime").addText(StringUtil.parseNull(paymentInfo2.get("tradetime")));
		}else{
			log.warn("Not found paymentInfo2 with relation_id " + relationId +" , stop calling webservice.");
			callEnabled = false;
		}
		
		log.debug("lori7:"+callEnabled);
		
		jt.update("insert into Ecar_insurance_ivr_log(log_guid,relation_id,stepdesc,param,createddate,log_type) values(?,?,?,?,?,?)", 
				new GUID().toString(),relationId,"调用前",StringUtil.substrByBytes(requestDoc.asXML(),2000,3),
				StringUtil.getDateStr(System.currentTimeMillis()),"1");
		
		if(callEnabled){
			log.debug("lori8");
			AcciCarApplyInfSoapBindingStub stub = new AcciCarApplyInfSoapBindingStub(new java.net.URL(endpoint),null);
			log.debug("lori9");
			String responseXML = stub.applyExecute(requestDoc.asXML());
			log.debug("lori10");
			jt.update("insert into Ecar_insurance_ivr_log(log_guid,relation_id,stepdesc,param,createddate,log_type) values(?,?,?,?,?,?)", 
					new GUID().toString(),relationId,"调用后",StringUtil.substrByBytes(responseXML,2000,3),
					StringUtil.getDateStr(System.currentTimeMillis()),"2");
			Document responseDoc = DocumentHelper.parseText(responseXML);
//				<Packet>
//				   <Head>
//				      <BusinessType>3</BusinessType>
//				      <AgencyCode>A1111</AgencyCode>
//				      <ProductCode>1111</ProductCode>
//				      <SeqNo>gzl11120130509000008</SeqNo>
//				      <ResponseCompleteMessageStatus>
//				         <MessageStatusCode>E0000</MessageStatusCode>
//				         <MessageStatusDescription>UnKnown Exception</MessageStatusDescription>
//				      </ResponseCompleteMessageStatus>
//				   </Head>
//				</Packet>
			String messageCode = responseDoc.selectSingleNode("/Packet/Head/ResponseCompleteMessageStatus/MessageStatusCode").getText();
			String messageDesc = responseDoc.selectSingleNode("/Packet/Head/ResponseCompleteMessageStatus/MessageStatusDescription").getText();
			String insuredName = "";
			String quantity = "";
			String prem = "";
			String policyNo = "";
			if("0000".equals(messageCode)){
				Element resultEle = (Element)responseDoc.selectSingleNode("/Packet/Body/Result");
				insuredName = resultEle.selectSingleNode("InsuredName").getText();
				quantity = resultEle.selectSingleNode("Quantity").getText();
				prem = resultEle.selectSingleNode("Prem").getText();
				policyNo = resultEle.selectSingleNode("PolicyNo").getText();
			}

			SimpleJdbcCall sp=new SimpleJdbcCall(dataSource);
    		sp.withProcedureName(procedureName);
    		Map<String,String> inParam = new HashMap<String,String>();
    		//v_MessageCode in,v_Messagedesc in,v_InsuredName in,v_ResQuantity in,v_prem in,v_PolicyNo in
    		inParam.put("v_relation_id", relationId);
    		inParam.put("v_MessageCode", messageCode);
    		inParam.put("v_Messagedesc", messageDesc);
    		inParam.put("v_InsuredName", insuredName);
    		inParam.put("v_ResQuantity", quantity);
    		inParam.put("v_prem", prem);
    		inParam.put("v_PolicyNo", policyNo);
    		
    		log.debug("Start call procedure : "+procedureName);
    		Map<String, Object> result = sp.execute(inParam);
    		for(String key : result.keySet()){
    			log.debug("Procedure return "+key+":"+result.get(key));
    		}
    		log.debug("End call procedure");
		}else{
			log.debug("callEnabled_false");
		}
	}
}
